import pandas as pd
import xlsxwriter
import json

def load_external_json(file_path):
    """动态加载外部JSON文件"""
    try:
        with open(file_path, 'r', encoding='utf-8') as f:
            return json.load(f)
    except FileNotFoundError:
        print(f"错误：文件 {file_path} 不存在")
        exit(1)
    except json.JSONDecodeError:
        print(f"错误：文件 {file_path} 格式无效")
        exit(1)

if __name__ == "__main__":
    # ✅ 使用原始字符串处理Windows路径（注意前缀r）
    json_data = load_external_json(r'D:\MyCodeWork\json2excel\json_str.txt')  # 路径
    
    df = pd.DataFrame(json_data).fillna('')


    with pd.ExcelWriter(r"D:\MyCodeWork\json2excel\json_excel.xlsx", engine="xlsxwriter") as writer:
        df.to_excel(writer, index=False, sheet_name="Sheet1")
        workbook = writer.book
        worksheet = writer.sheets["Sheet1"]
        text_format = workbook.add_format({'num_format': '@'})
        worksheet.set_column('A:Z', 20, text_format)